package com.heliang.dataaccess.dao.impl;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.util.Log;

import com.heliang.dataaccess.bean.MailListBean;
import com.heliang.dataaccess.dao.BaseDao;
import com.heliang.dataaccess.db.DataBaseHelper;
import com.heliang.util.Constants;
import com.heliang.util.DataUtil;

public class MaillistDaoImpl extends BaseDao{

	public MaillistDaoImpl(Context context) {
		super(context);
	}
	

	/** 
	 * 保存通讯录信息
	 * @param InfoList
	 * @return
	 */
	public boolean insert(List<MailListBean> mailListBeans) {
		boolean result = false;
		try {
			db = databaseHelper.getWritableDatabase();
			for (int i = 0; i < mailListBeans.size(); i++) {
				try {
					MailListBean mailListBean = mailListBeans.get(i);
					db.execSQL("replace into " + DataBaseHelper.T_MEMBER_BOOK + 
							"(memberid, user_id, user_name,email, gender, post, company_name, tel, mobile, cat_id, cat_name, province, city, district, fax, addr, imgurl, imgpath, imgname, created, pinyin,url)" +
							" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
							new Object[]{
							mailListBean.getId(),
							mailListBean.getUserid(),
							mailListBean.getUsername(),
							mailListBean.getEmail(),
							mailListBean.getGender(),
							mailListBean.getPost(),
							mailListBean.getCompanyname(),
							mailListBean.getTel(),
							mailListBean.getMobile(),
							mailListBean.getCatid(),
							mailListBean.getCatname(),
							mailListBean.getProvince(),
							mailListBean.getCity(),
							mailListBean.getDistrict(),
							mailListBean.getFax(),
							mailListBean.getAddr(),
							mailListBean.getImgurl(),
							mailListBean.getImgpath(),
							mailListBean.getImgname(),
							mailListBean.getCreated(),
							mailListBean.getPinyin(),
							mailListBean.getUrl()
							});
				} catch (Exception e) {
					// TODO: handle exception
					Log.e(Constants.TAG, e.getMessage());
				}
			}
			result = true;
		} catch (Exception e) {
			// TODO: handle exception
			Log.e(Constants.TAG, e.getMessage());
		}finally{
			closeDb();
		}
		return result;
	}
	
	public void deleteAll(){
		String sqlStr = "delete from " + DataBaseHelper.T_MEMBER_BOOK;
		db = databaseHelper.getWritableDatabase();
		db.execSQL(sqlStr);
	}
	
	/**
	 * 删除数据
	 * @param int[]  
	 * @return boolean
	 */
	public boolean delete(int[] idArray){
		boolean result = false;
		try {
			
			if (idArray == null || idArray.length ==0) {
				return result;
			}
			
			String sqlStr = "delete from " + DataBaseHelper.T_MEMBER_BOOK + " WHERE memberid in(";
			for (int i = 0; i < idArray.length; i++) {
				if (i != (idArray.length - 1)) {
					sqlStr += idArray[i] + ",";
				}else {
					sqlStr += idArray[i] + ")";
				}
				
			}
			db = databaseHelper.getWritableDatabase();
			db.execSQL(sqlStr);
			
			result = true;
		} catch (Exception e) {
			// TODO: handle exception
			Log.e(Constants.TAG, e.getMessage());
		}finally{
			closeDb();
		}
		return result;
	}
	
	
	/**
	 * 通过关键词查询数据(查询全部数据)
	 * @return
	 */
	public List<MailListBean> queryByKey(String content){
		List<MailListBean> mailListBeans = new ArrayList<MailListBean>();
		try {
			
			db = databaseHelper.getReadableDatabase();
			Cursor cursor = null ;
			String sqlString = null;
			if(content == null){
				sqlString = "select * from " + DataBaseHelper.T_MEMBER_BOOK + " order by pinyin";
			}else{
				sqlString = "select * from " + DataBaseHelper.T_MEMBER_BOOK +
				" where user_name like " + "'"+content+"'"+ 
				" or company_name like " +"'"+content+"'"+
				" or post like " +"'"+content+"'"+
				" or pinyin like " +"'"+content+"'"+
				" or city like " +"'"+content+"'"+
				" order by created";
			}
			if(sqlString!=null){
				cursor = db.rawQuery(sqlString ,null);
			}
			while (cursor.moveToNext()) {
				MailListBean mailListBean = new MailListBean();
				mailListBean.setId(cursor.getInt(cursor.getColumnIndex("memberid")));
				mailListBean.setUserid(cursor.getInt(cursor.getColumnIndex("user_id")));
				mailListBean.setUsername(cursor.getString(cursor.getColumnIndex("user_name")));
				mailListBean.setEmail(cursor.getString(cursor.getColumnIndex("email")));
				mailListBean.setGender(cursor.getInt(cursor.getColumnIndex("gender")));
				mailListBean.setPost(cursor.getString(cursor.getColumnIndex("post")));
				mailListBean.setCompanyname(cursor.getString(cursor.getColumnIndex("company_name")));
				mailListBean.setTel(cursor.getString(cursor.getColumnIndex("tel")));
				mailListBean.setMobile(cursor.getString(cursor.getColumnIndex("mobile")));
				mailListBean.setCatid(cursor.getInt(cursor.getColumnIndex("cat_id")));
				mailListBean.setCatname(cursor.getString(cursor.getColumnIndex("cat_name")));
				mailListBean.setProvince(cursor.getString(cursor.getColumnIndex("province")));
				mailListBean.setCity(cursor.getString(cursor.getColumnIndex("city")));
				mailListBean.setDistrict(cursor.getString(cursor.getColumnIndex("district")));
				mailListBean.setFax(cursor.getString(cursor.getColumnIndex("fax")));
				mailListBean.setAddr(cursor.getString(cursor.getColumnIndex("addr")));
				mailListBean.setImgurl(cursor.getString(cursor.getColumnIndex("imgurl")));
				mailListBean.setImgpath(cursor.getString(cursor.getColumnIndex("imgpath")));
				mailListBean.setImgname(cursor.getString(cursor.getColumnIndex("imgname")));
				mailListBean.setCreated(cursor.getInt(cursor.getColumnIndex("created")));
				mailListBean.setPinyin(cursor.getString(cursor.getColumnIndex("pinyin")));
				mailListBean.setUrl(cursor.getString(cursor.getColumnIndex("url")));
				mailListBeans.add(mailListBean);
			}
			
			if (cursor != null) {
				cursor.close();
			}
			
		} catch (Exception e) {
			// TODO: handle exception
			Log.e(Constants.TAG, e.getMessage());
		}finally{
			closeDb();
		}
		
		return mailListBeans;
	}
	
	/**
	 * 通过关键和分类id词查询数据(查询全部数据)
	 * @return
	 */
	public List<MailListBean> catQueryByKey(String content,int catid){
		List<MailListBean> mailListBeans = new ArrayList<MailListBean>();
		try {
			
			db = databaseHelper.getReadableDatabase();
			Cursor cursor = null ;
			String sqlString = null;
			if(content == null){
				sqlString = "select * from " + DataBaseHelper.T_MEMBER_BOOK +" where cat_id="+catid+ " order by pinyin";
			}else{
				sqlString = "select * from " + DataBaseHelper.T_MEMBER_BOOK +
				" where "+"("+"user_name like " + "'"+content+"'"+
				" or company_name like " + "'"+content+"'"+
				" or post like " + "'"+content+"'"+
				" or pinyin like " + "'"+content+"'"+
				" or city like " + "'"+content+"'"+
				") and cat_id = "+catid+ " order by pinyin";
			}
			if(sqlString!=null){
				cursor = db.rawQuery(sqlString ,null);
			}
			while (cursor.moveToNext()) {
				MailListBean mailListBean = new MailListBean();
				mailListBean.setId(cursor.getInt(cursor.getColumnIndex("memberid")));
				mailListBean.setUserid(cursor.getInt(cursor.getColumnIndex("user_id")));
				mailListBean.setUsername(cursor.getString(cursor.getColumnIndex("user_name")));
				mailListBean.setEmail(cursor.getString(cursor.getColumnIndex("email")));
				mailListBean.setGender(cursor.getInt(cursor.getColumnIndex("gender")));
				mailListBean.setPost(cursor.getString(cursor.getColumnIndex("post")));
				mailListBean.setCompanyname(cursor.getString(cursor.getColumnIndex("company_name")));
				mailListBean.setTel(cursor.getString(cursor.getColumnIndex("tel")));
				mailListBean.setMobile(cursor.getString(cursor.getColumnIndex("mobile")));
				mailListBean.setCatid(cursor.getInt(cursor.getColumnIndex("cat_id")));
				mailListBean.setCatname(cursor.getString(cursor.getColumnIndex("cat_name")));
				mailListBean.setProvince(cursor.getString(cursor.getColumnIndex("province")));
				mailListBean.setCity(cursor.getString(cursor.getColumnIndex("city")));
				mailListBean.setDistrict(cursor.getString(cursor.getColumnIndex("district")));
				mailListBean.setFax(cursor.getString(cursor.getColumnIndex("fax")));
				mailListBean.setAddr(cursor.getString(cursor.getColumnIndex("addr")));
				mailListBean.setImgurl(cursor.getString(cursor.getColumnIndex("imgurl")));
				mailListBean.setImgpath(cursor.getString(cursor.getColumnIndex("imgpath")));
				mailListBean.setImgname(cursor.getString(cursor.getColumnIndex("imgname")));
				mailListBean.setCreated(cursor.getInt(cursor.getColumnIndex("created")));
				mailListBean.setPinyin(cursor.getString(cursor.getColumnIndex("pinyin")));
				mailListBean.setUrl(cursor.getString(cursor.getColumnIndex("url")));
				mailListBeans.add(mailListBean);
			}
			
			if (cursor != null) {
				cursor.close();
			}
			
		} catch (Exception e) {
			// TODO: handle exception
			Log.e(Constants.TAG, e.getMessage());
		}finally{
			closeDb();
		}
		
		return mailListBeans;
	}
	
	/**
	 * 通过关键词查询数据(查询全部数据)
	 * @return
	 */
	public List<MailListBean> query(){
		List<MailListBean> mailListBeans = new ArrayList<MailListBean>();
		try {
			
			db = databaseHelper.getReadableDatabase();
			Cursor cursor = null ;
			String sqlString = null;
			sqlString = "select * from " + DataBaseHelper.T_MEMBER_BOOK + " order by pinyin";
	
			if(sqlString!=null){
				cursor = db.rawQuery(sqlString ,null);
			}
			while (cursor.moveToNext()) {
				MailListBean mailListBean = new MailListBean();
				mailListBean.setId(cursor.getInt(cursor.getColumnIndex("memberid")));
				mailListBean.setUserid(cursor.getInt(cursor.getColumnIndex("user_id")));
				mailListBean.setUsername(cursor.getString(cursor.getColumnIndex("user_name")));
				mailListBean.setEmail(cursor.getString(cursor.getColumnIndex("email")));
				mailListBean.setGender(cursor.getInt(cursor.getColumnIndex("gender")));
				mailListBean.setPost(cursor.getString(cursor.getColumnIndex("post")));
				mailListBean.setCompanyname(cursor.getString(cursor.getColumnIndex("company_name")));
				mailListBean.setTel(cursor.getString(cursor.getColumnIndex("tel")));
				mailListBean.setMobile(cursor.getString(cursor.getColumnIndex("mobile")));
				mailListBean.setCatid(cursor.getInt(cursor.getColumnIndex("cat_id")));
				mailListBean.setCatname(cursor.getString(cursor.getColumnIndex("cat_name")));
				mailListBean.setProvince(cursor.getString(cursor.getColumnIndex("province")));
				mailListBean.setCity(cursor.getString(cursor.getColumnIndex("city")));
				mailListBean.setDistrict(cursor.getString(cursor.getColumnIndex("district")));
				mailListBean.setFax(cursor.getString(cursor.getColumnIndex("fax")));
				mailListBean.setAddr(cursor.getString(cursor.getColumnIndex("addr")));
				mailListBean.setImgurl(cursor.getString(cursor.getColumnIndex("imgurl")));
				mailListBean.setImgpath(cursor.getString(cursor.getColumnIndex("imgpath")));
				mailListBean.setImgname(cursor.getString(cursor.getColumnIndex("imgname")));
				mailListBean.setCreated(cursor.getInt(cursor.getColumnIndex("created")));
				mailListBean.setPinyin(cursor.getString(cursor.getColumnIndex("pinyin")));
				mailListBean.setUrl(cursor.getString(cursor.getColumnIndex("url")));
				DataUtil.dateDiff((long)mailListBean.getCreated());
				long day = DataUtil.dateDiff((long)mailListBean.getCreated());
				if(day>=0 && day<7){//判断是否是最新会员
					Constants.NEWEST_MILLIST.add(mailListBean.getId());
				}
				Log.e("此会员创建时间距现在的天数=="+mailListBean.getUsername()+"===", day+"");
				mailListBeans.add(mailListBean);
			}
			
			if (cursor != null) {
				cursor.close();
			}
			
		} catch (Exception e) {
			// TODO: handle exception
			Log.e(Constants.TAG, e.getMessage());
		}finally{
			closeDb();
		}
		
		return mailListBeans;
	}
	/**
	 * 通过user_id查询数据(查询全部数据)
	 * @return
	 */
	public MailListBean query(int userid){
		MailListBean mailListBean = null;
		try {
			
			db = databaseHelper.getReadableDatabase();
			Cursor cursor = null ;
			String sqlString = null;
			sqlString = "select * from " + DataBaseHelper.T_MEMBER_BOOK + " where user_id="+ userid;
			cursor = db.rawQuery(sqlString ,null);
			while (cursor.moveToNext()) {
				mailListBean = new MailListBean();
				mailListBean.setId(cursor.getInt(cursor.getColumnIndex("memberid")));
				mailListBean.setUserid(cursor.getInt(cursor.getColumnIndex("user_id")));
				mailListBean.setUsername(cursor.getString(cursor.getColumnIndex("user_name")));
				mailListBean.setEmail(cursor.getString(cursor.getColumnIndex("email")));
				mailListBean.setGender(cursor.getInt(cursor.getColumnIndex("gender")));
				mailListBean.setPost(cursor.getString(cursor.getColumnIndex("post")));
				mailListBean.setCompanyname(cursor.getString(cursor.getColumnIndex("company_name")));
				mailListBean.setTel(cursor.getString(cursor.getColumnIndex("tel")));
				mailListBean.setMobile(cursor.getString(cursor.getColumnIndex("mobile")));
				mailListBean.setCatid(cursor.getInt(cursor.getColumnIndex("cat_id")));
				mailListBean.setCatname(cursor.getString(cursor.getColumnIndex("cat_name")));
				mailListBean.setProvince(cursor.getString(cursor.getColumnIndex("province")));
				mailListBean.setCity(cursor.getString(cursor.getColumnIndex("city")));
				mailListBean.setDistrict(cursor.getString(cursor.getColumnIndex("district")));
				mailListBean.setFax(cursor.getString(cursor.getColumnIndex("fax")));
				mailListBean.setAddr(cursor.getString(cursor.getColumnIndex("addr")));
				mailListBean.setImgurl(cursor.getString(cursor.getColumnIndex("imgurl")));
				mailListBean.setImgpath(cursor.getString(cursor.getColumnIndex("imgpath")));
				mailListBean.setImgname(cursor.getString(cursor.getColumnIndex("imgname")));
				mailListBean.setCreated(cursor.getInt(cursor.getColumnIndex("created")));
				mailListBean.setPinyin(cursor.getString(cursor.getColumnIndex("pinyin")));
				mailListBean.setUrl(cursor.getString(cursor.getColumnIndex("url")));
			}
			if (cursor != null) {
				cursor.close();
			}
		} catch (Exception e) {
			// TODO: handle exception
			Log.e(Constants.TAG, e.getMessage());
		}finally{
			closeDb();
		}
		
		return mailListBean;
	}
	/**
	 * 通过关键词查询数据(查询全部数据)
	 * @return
	 */
	public List<MailListBean> catQuery(int catid){
		List<MailListBean> mailListBeans = new ArrayList<MailListBean>();
		try {
			
			db = databaseHelper.getReadableDatabase();
			Cursor cursor = null ;
			String sqlString = null;
			sqlString = "select * from " + DataBaseHelper.T_MEMBER_BOOK +" where cat_id ="+catid+ " order by pinyin";
			
			if(sqlString!=null){
				cursor = db.rawQuery(sqlString ,null);
			}
			while (cursor.moveToNext()) {
				MailListBean mailListBean = new MailListBean();
				mailListBean.setId(cursor.getInt(cursor.getColumnIndex("memberid")));
				mailListBean.setUserid(cursor.getInt(cursor.getColumnIndex("user_id")));
				mailListBean.setUsername(cursor.getString(cursor.getColumnIndex("user_name")));
				mailListBean.setEmail(cursor.getString(cursor.getColumnIndex("email")));
				mailListBean.setGender(cursor.getInt(cursor.getColumnIndex("gender")));
				mailListBean.setPost(cursor.getString(cursor.getColumnIndex("post")));
				mailListBean.setCompanyname(cursor.getString(cursor.getColumnIndex("company_name")));
				mailListBean.setTel(cursor.getString(cursor.getColumnIndex("tel")));
				mailListBean.setMobile(cursor.getString(cursor.getColumnIndex("mobile")));
				mailListBean.setCatid(cursor.getInt(cursor.getColumnIndex("cat_id")));
				mailListBean.setCatname(cursor.getString(cursor.getColumnIndex("cat_name")));
				mailListBean.setProvince(cursor.getString(cursor.getColumnIndex("province")));
				mailListBean.setCity(cursor.getString(cursor.getColumnIndex("city")));
				mailListBean.setDistrict(cursor.getString(cursor.getColumnIndex("district")));
				mailListBean.setFax(cursor.getString(cursor.getColumnIndex("fax")));
				mailListBean.setAddr(cursor.getString(cursor.getColumnIndex("addr")));
				mailListBean.setImgurl(cursor.getString(cursor.getColumnIndex("imgurl")));
				mailListBean.setImgpath(cursor.getString(cursor.getColumnIndex("imgpath")));
				mailListBean.setImgname(cursor.getString(cursor.getColumnIndex("imgname")));
				mailListBean.setCreated(cursor.getInt(cursor.getColumnIndex("created")));
				mailListBean.setPinyin(cursor.getString(cursor.getColumnIndex("pinyin")));
				mailListBean.setUrl(cursor.getString(cursor.getColumnIndex("url")));
				mailListBeans.add(mailListBean);
			}
			
			if (cursor != null) {
				cursor.close();
			}
			
		} catch (Exception e) {
			// TODO: handle exception
			Log.e(Constants.TAG, e.getMessage());
		}finally{
			closeDb();
		}
		
		return mailListBeans;
	}
	/**
	 * 通过分类查询数据(查询全部数据)
	 * @return
	 */
	public List<MailListBean> queryByCat(String content){
		List<MailListBean> mailListBeans = new ArrayList<MailListBean>();
		try {
			
			db = databaseHelper.getReadableDatabase();
			Cursor cursor = db.rawQuery("select * from " + DataBaseHelper.T_MEMBER_BOOK + " where user_name="+ "content" + " order by created" ,null);
			while (cursor.moveToNext()) {
				MailListBean mailListBean = new MailListBean();
				mailListBean.setId(cursor.getInt(cursor.getColumnIndex("memberid")));
				mailListBean.setUserid(cursor.getInt(cursor.getColumnIndex("user_id")));
				mailListBean.setUsername(cursor.getString(cursor.getColumnIndex("user_name")));
				mailListBean.setEmail(cursor.getString(cursor.getColumnIndex("email")));
				mailListBean.setGender(cursor.getInt(cursor.getColumnIndex("gender")));
				mailListBean.setPost(cursor.getString(cursor.getColumnIndex("post")));
				mailListBean.setCompanyname(cursor.getString(cursor.getColumnIndex("company_name")));
				mailListBean.setTel(cursor.getString(cursor.getColumnIndex("tel")));
				mailListBean.setMobile(cursor.getString(cursor.getColumnIndex("mobile")));
				mailListBean.setCatid(cursor.getInt(cursor.getColumnIndex("cat_id")));
				mailListBean.setCatname(cursor.getString(cursor.getColumnIndex("cat_name")));
				mailListBean.setProvince(cursor.getString(cursor.getColumnIndex("province")));
				mailListBean.setCity(cursor.getString(cursor.getColumnIndex("city")));
				mailListBean.setDistrict(cursor.getString(cursor.getColumnIndex("district")));
				mailListBean.setFax(cursor.getString(cursor.getColumnIndex("fax")));
				mailListBean.setAddr(cursor.getString(cursor.getColumnIndex("addr")));
				mailListBean.setImgurl(cursor.getString(cursor.getColumnIndex("imgurl")));
				mailListBean.setImgpath(cursor.getString(cursor.getColumnIndex("imgpath")));
				mailListBean.setImgname(cursor.getString(cursor.getColumnIndex("imgname")));
				mailListBean.setCreated(cursor.getInt(cursor.getColumnIndex("created")));
				mailListBean.setPinyin(cursor.getString(cursor.getColumnIndex("pinyin")));
				mailListBean.setUrl(cursor.getString(cursor.getColumnIndex("url")));
				mailListBeans.add(mailListBean);
			}
			
			if (cursor != null) {
				cursor.close();
			}
			
		} catch (Exception e) {
			// TODO: handle exception
			Log.e(Constants.TAG, e.getMessage());
		}finally{
			closeDb();
		}
		
		return mailListBeans;
	}
	
	
}
